﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Framework.Extensions;
namespace Framework.Data.Common
{
    public abstract class DatabaseQueryingImplementationDetailsBase : IDatabaseQueryingImplementationDetails
    {
        public abstract bool SupportsPagedStatements { get; }
        public abstract bool SupportsUpsertStatement { get; }

        public virtual string GetSelectStatement(string objectName, string columns = "*", string where = "", string orderBy = "")
        {
            string sql = string.Format("select " + columns + " from {0} ", objectName);
            if (!string.IsNullOrEmpty(where)) sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "where " + where;
            if (!string.IsNullOrEmpty(orderBy)) sql += orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase) ? orderBy : "order by " + orderBy;
            return sql;
        }

        public abstract string GetPagedStatement(string objectName, string orderBy, int startingRecord, int recordCount, string columns = "*", string where = "");
        public abstract string GetUpsertStatement(string objectName, IEnumerable<string> keyColumnNames, IEnumerable<string> editableColumnNames);

        public virtual string GetCountStatement(string objectName, string where = "")
        {
            string sql = string.Format("select count(*) from {0} ", objectName);
            if (!string.IsNullOrEmpty(where)) sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "where " + where;
            return sql;
        }

        public virtual string GetDeleteStatement(string objectName, string where = "")
        {
            string sql = string.Format("delete from {0} ", objectName);
            if (!string.IsNullOrEmpty(where)) sql += where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase) ? where : "where " + where;
            return sql;
        }


        public string GetInsertStatement(string objectName, IEnumerable<string> columnNames)
        {
            var columns = string.Join(", ", columnNames);
            var values = string.Join(", ", columnNames.Count().ToList(x => "@" + x.ToString()));
            return string.Format("insert into {0}({1}) values({2}) ", objectName, columns, values);
        }

        public string GetUpdateStatement(string objectName, IEnumerable<string> keyColumnNames, IEnumerable<string> updatedColumnNames)
        {
            var keys = keyColumnNames.ToList();
            var keyCount = keys.Count();
            var keysStatement = string.Join(" and ", keyCount.ToList(x => keys[x] + " = @" + x.ToString()));

            var columns = updatedColumnNames.ToList();
            var columnsCount = columns.Count();
            var columnsStatement = string.Join(", ", columnsCount.ToList(x => columns[x] + " = @" + (x + keyCount).ToString()));

            return string.Format("update {0} set {1} where {2}", objectName, columnsStatement, keysStatement);
        }
    }
}
